
CREATE TABLE [Account] (
	[UserId] varchar(20) PRIMARY KEY,
	[Email] varchar(80) NOT NULL,
	[FirstName] varchar(80) NOT NULL,
	[LastName] varchar(80) NOT NULL,
	[Status] varchar(2) NULL,
	[Addr1] varchar(80) NOT NULL,
	[Addr2] varchar(80) NULL,
	[City] varchar(80) NOT NULL,
	[State] varchar(80) NOT NULL,
	[Zip] varchar(20) NOT NULL,
	[Country] varchar(20) NOT NULL,
	[Phone] varchar(20) NOT NULL,
	[LangPref] varchar(80) NOT NULL,
	[FavCategory] varchar(30) NULL,
	[MyListOpt] int NULL,
	[BannerOpt] int NULL,
	[Password] varchar(20) NOT NULL
)



CREATE TABLE [Category] (
	[CatId] varchar(10) PRIMARY KEY,
	[Name] varchar(80) NULL,
	[Descn] varchar(255) NULL,
	[BannerData] [varchar](255) NULL
)


CREATE TABLE [Product] (
	[ProductId] varchar(10) PRIMARY KEY,
	[Category] varchar(10) NOT NULL REFERENCES [Category]([CatId]),
	[Name] varchar(80) NULL,
	[Descn] varchar(255) NULL
)




CREATE TABLE [Item] (
	[ItemId] [varchar](10)  PRIMARY KEY,
	[ProductId] [varchar](10) NOT NULL REFERENCES [Product] ([ProductId]),
	[ListPrice] [decimal](10, 2) NULL,
	[UnitCost] [decimal](10, 2) NULL,
	[Status] [varchar](2) NULL,
	[Description] [varchar](80) NULL,
	[InventoryQty] [int] NULL
)

CREATE TABLE [Orders] (
	[OrderId] int IDENTITY PRIMARY KEY,
	[UserId] varchar(20) NOT NULL REFERENCES [Account] ([UserId]),
	[OrderDate] datetime NOT NULL,
	[ShipAddr1] varchar(80) NOT NULL,
	[ShipAddr2] varchar(80) NULL,
	[ShipCity] varchar(80) NOT NULL,
	[ShipState] varchar(80) NOT NULL,
	[ShipZip] varchar(20) NOT NULL,
	[ShipCountry] varchar(20) NOT NULL,
	[BillAddr1] varchar(80) NOT NULL,
	[BillAddr2] varchar(80) NULL,
	[BillCity] varchar(80) NOT NULL,
	[BillState] varchar(80) NOT NULL,
	[BillZip] varchar(20) NOT NULL,
	[BillCountry] varchar(20) NOT NULL,
	[TotalPrice] decimal(10, 2) NOT NULL,
	[BillToFirstName] varchar(80) NOT NULL,
	[BillToLastName] varchar(80) NOT NULL,
	[ShipToFirstName] varchar(80) NOT NULL,
	[ShipToLastName] varchar(80) NOT NULL,
	[CreditCard] varchar(20) NOT NULL,
	[ExprDate] varchar(7) NOT NULL,
	[CardType] varchar(40) NOT NULL
	
)

CREATE TABLE [LineItem] (
	[LineItemId] int IDENTITY PRIMARY KEY,
	[OrderId] int NOT NULL REFERENCES [Orders]([OrderId]),
	[LineNum] int NOT NULL,
	[ItemId] varchar(10) REFERENCES [Item]([ItemId]),
	[Quantity] int NOT NULL,
	[UnitPrice] decimal(10, 2) NOT NULL
)


CREATE UNIQUE  INDEX [IX_LineItem] ON [dbo].[LineItem] 
(
	[OrderId] ASC,
	[LineNum] ASC
)
GO

CREATE INDEX [IxItem] ON [Item]([ProductId], [ItemId], [ListPrice])
CREATE INDEX [IxProduct1] ON [Product]([Name])
CREATE INDEX [IxProduct2] ON [Product]([Category])
CREATE INDEX [IxProduct3] ON [Product]([Category], [Name])
CREATE INDEX [IxProduct4] ON [Product]([Category], [ProductId], [Name])